
package cn.hpclub.server.service;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @FileName: CmccOrderGcgmService.java
 * @Author zhl
 * @Description:
 * @Date 2016年6月27日 下午2:38:57
 * @CopyRight ZTE Corporation
 */
public class CmccOrderGcgmService {

    private static final Logger    logger  = LoggerFactory.getLogger(CmccOrderGcgmService.class);
    public static CmccOrderGcgmService service = new CmccOrderGcgmService();

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " from cmcc_order_gcgm cog where 1=1 ";
        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += " order by cog.create_time desc ";
        return sqlExceptSelect;
    }

    private String buildQuery(Entry<String, String> item){
        if("start_time".equalsIgnoreCase(item.getKey())){
            String start_time = "'" + item.getValue().replaceAll(" ", "") + " 00:00:00'";
            return " and cog.create_time >= " + start_time + "  ";
        }
        if("end_time".equalsIgnoreCase(item.getKey())){
            String end_time = "'" + item.getValue().replaceAll(" ", "") + " 23:59:59 '";
            return " and cog.create_time <= " + end_time + " ";
        }
        if("emp_name".equalsIgnoreCase(item.getKey())){
            return " and cog.emp_name like '%" + item.getValue() + "%'";
        }
        if("org_id".equalsIgnoreCase(item.getKey())){
            return " and cog.org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true)
                    + ") ";
        }
        return " and cog." + item.getKey() + " like '%" + item.getValue() + "%'";
    }





    /**
     * @describe 获取基础拜访列表的excel文件
     * @param searchInfo
     *            查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = " SELECT * FROM cmcc_order_gcgm cog WHERE 1=1 ";
        for(Entry<String, String> item : searchInfo.entrySet()){
            sql += buildQuery(item);
        }
        sql += " order by cog.create_time desc  ";
        List<Record> seq = Db.find(sql);
        for (Record record : seq) {
            record.set("op_month",record.get("op_month")+" 个月");
        }
        String[] excelHeaders = new String[] { "名称", "手机号码", "业务时长", "资费编码", "店员工号",
                "店员电话", "店员名称", "是否客户经理", "城市", "区县",
                "渠道名称", "渠道编码", "渠道类型", "办理时间" };

        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[] { "business_name", "phone_no", "op_month", "prod_prcid", "emp_code", "emp_mobile",
                "emp_name", "account_manager", "city", "display", "channel_name", "channel_code",
                "channel_type", "create_time" };
        try{
            exportUtils.exportExcel(keys, seq, "yyyy-MM-dd HH:ss:mm", 1);// 此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        }
        catch(FileNotFoundException e){
            logger.error("CmccOrderGcgmService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"
                    + e.getMessage());
        }
        catch(IOException ex){
            logger.error("CmccOrderGcgmService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"
                    + ex.getMessage());
        }
        return workbook;
    }









}
